﻿using System;
using System.Collections.Generic;
using System.Text;
using DBAccess;
using System.Data;

namespace SD.Database
{
    public class DataAccess
    {
        public static DataSet GetAllNews()
        {
            return DAL.GetInstance().GetData("Select * FROM News Order by NewsDate dsc");
        }
        public static DataSet NumberOfActivePlayers()
        {
            return DAL.GetInstance().GetData("SELECT COUNT(PlayerId) AS \"Number of players\" FROM Players WHERE (PlayerId > 1) AND ((SELECT COUNT(OwnerID) AS Expr1 FROM Planets WHERE (OwnerID = Players.PlayerId)) > 0)");
        }
        public static DataSet GetStats()
        {
            return DAL.GetInstance().GetData("SELECT AVG(Attack) AS \"Avarage Attack\", AVG(Defense) AS \"Avarage Defense\", AVG(Speed) AS \"Avarage Speed\", AVG(Culture) AS \"Avarage Culture\", AVG(Production) AS \"Avarage Production\", AVG(Science) AS \"Avarage Science\", AVG(Growth) AS \"Avarage Growth\", AVG(Biology) AS \"Avarage Biology\", AVG(Economy) AS \"Avarage Economy\", AVG(Energy) AS \"Avarage Energy\", AVG(Math) AS \"Avarage Math\", AVG(Physics) AS \"Avarage Physics\", AVG(Social) AS \"Avarage Social\", AVG(CurrentCulture) AS \"Avarage Culture\" FROM Players Where PlayerID > 1");
        }
        public static DataSet GetBattlesForPlayer(int pId)
        {
            return TimeHelper.ChangeTime(DAL.GetInstance().GetData("SELECT Distinct (SELECT PlayerName FROM Players WHERE (PlayerId = Battles.AttackerID)) AS Attacker, Battles.AttackerCV, (SELECT PlayerName FROM Players AS Players_2 WHERE (PlayerId = Battles.DefenderId)) AS Defender, Battles.DefenderCV, Battles.Date, Systems.SystemName AS System, Battles.PlanetId AS Planet, (SELECT PlayerName FROM Players AS Players_3 WHERE (PlayerId = Battles.WinnerId)) AS Winner FROM Systems INNER JOIN Battles ON Systems.SystemID = Battles.SystemId CROSS JOIN Players AS Players_1 WHERE (Battles.AttackerID = @param0) OR (Battles.DefenderId = @param0) ORDER BY Battles.Date DESC", new List<object>() { pId }), pId);
        }
        public static DataSet GetIntelForPlayer(int pId)
        {
            return TimeHelper.ChangeTime(DAL.GetInstance().GetData("SELECT Players.PlayerName, Intel.DateOfScan, Intel.Attack, Intel.Defense, Intel.Speed, Intel.Culture, Intel.Production, Intel.Science, Intel.Growth, Intel.Biology, Intel.Economy, Intel.Energy, Intel.Math, Intel.Physics, Intel.Social, Intel.CurrentCulture FROM Intel INNER JOIN Players ON Players.PlayerId = Intel.pId1 WHERE (Intel.pId = @param0)", new List<object>() { pId }), pId);
        }
        public static DataSet GetTAs(int pId)
        {
            return DAL.GetInstance().GetData("SELECT (SELECT PlayerName FROM Players WHERE (PlayerId = Agreements.pId1)) AS \"From\", (SELECT PlayerName FROM Players AS Players_1 WHERE (PlayerId = Agreements.pId2)) AS \"To\", Status, TypeA as \"Type\" FROM Agreements WHERE (TypeA = 0) AND (pId1 = @param0) OR (TypeA = 0) AND (pId2 = @param0)", new List<object>() { pId });
        }
        public static DataSet GetSAs(int pId)
        {
            return DAL.GetInstance().GetData("SELECT (SELECT PlayerName FROM Players WHERE (PlayerId = Agreements.pId1)) AS \"From\", (SELECT PlayerName FROM Players AS Players_1 WHERE (PlayerId = Agreements.pId2)) AS \"To\", Status, TypeA as \"Type\" FROM Agreements WHERE (TypeA = 1) AND (pId1 = @param0) OR (TypeA = 1) AND (pId2 = @param0)", new List<object>() { pId });
        }
        public static DataSet GetAllPlayers()
        {
            return DAL.GetInstance().GetData("SELECT [PlayerId], [PlayerName] FROM [Players] WHERE ([PlayerId] > 1)");
        }
        public static DataSet GetFleetsOnAir(int pId)
        {
            return TimeHelper.ChangeTime(DAL.GetInstance().GetData("SELECT ColonyShips, Transports, Crusiers, Destroyers, Battleships, FromSystem, FromPlanet, ToPlanet, ToSystem, ArrivialTime AS \"Time\" FROM Fleets WHERE (OwnerID = @param0) ORDER BY Time DESC", new List<object>() { pId }), pId);
        }
        public static DataSet GetFleetsOnPlanets(int pID)
        {
            DAL dal = DAL.GetInstance();
            string cmd = "";

            if (dal.DataSource == DataSources.MSSQL)
                cmd = @"SELECT Planets.SystemID, Planets.PlanetID, Floor(Planets.ColonyShips) AS ColonyShips, Floor(Planets.Transports) AS Transports, Floor(Planets.Destroyers) AS Destroyers, Floor(Planets.Battleships) AS Battleships, Floor(Planets.Crusiers) AS Cruisers, Systems.SystemName + ' #' + CAST(Planets.PlanetID AS nvarchar) AS Expr1 FROM Planets INNER JOIN Systems ON Planets.SystemID = Systems.SystemID WHERE 
            (Planets.FleetOwnerID = @param0) AND (Floor(Planets.ColonyShips) + Floor(Planets.Transports) + Floor(Planets.Destroyers) + Floor(Planets.Battleships) + Floor(Planets.Crusiers) > 0) 
            OR (Planets.FleetOwnerID = 0) AND (Floor(Planets.ColonyShips) + Floor(Planets.Transports) + Floor(Planets.Destroyers) + Floor(Planets.Battleships) + Floor(Planets.Crusiers) > 0) AND (Planets.OwnerID = @param0)";
            else if (dal.DataSource == DataSources.MySQL)
                cmd = @"SELECT Planets.SystemID, Planets.PlanetID, Floor(Planets.ColonyShips) AS ColonyShips, Floor(Planets.Transports) AS Transports, Floor(Planets.Destroyers) AS Destroyers, Floor(Planets.Battleships) AS Battleships, Floor(Planets.Crusiers) AS Cruisers,Cast( Concat( Systems.SystemName, Concat( ' #' , Planets.PlanetID ))as char) AS Expr1 FROM Planets INNER JOIN Systems ON Planets.SystemID = Systems.SystemID WHERE 
            (Planets.FleetOwnerID = @param0) AND (Floor(Planets.ColonyShips) + Floor(Planets.Transports) + Floor(Planets.Destroyers) + Floor(Planets.Battleships) + Floor(Planets.Crusiers) > 0) 
            OR (Planets.FleetOwnerID = 0) AND (Floor(Planets.ColonyShips) + Floor(Planets.Transports) + Floor(Planets.Destroyers) + Floor(Planets.Battleships) + Floor(Planets.Crusiers) > 0) AND (Planets.OwnerID = @param0)";

            return dal.GetData(cmd, new List<object>() { pID });
        }
        public static DataSet GetSystemsInRange(int pId)
        {
            return DAL.GetInstance().GetData(@"SELECT SystemID, SystemName, X, Y FROM Systems WHERE 
                (SQRT(POWER(X - (SELECT X FROM Systems AS Systems_2 WHERE (SystemID = (SELECT OrginID FROM Players WHERE (PlayerId = @param0)))), 2) 
                + POWER(Y - (SELECT Y FROM Systems AS Systems_1 WHERE (SystemID = (SELECT OrginID FROM Players AS Players_2 WHERE (PlayerId = @param0)))), 2)) 
                <= (SELECT Biology FROM Players AS Players_1 WHERE (PlayerId = @param0)) / 2) AND 
                (SystemID <= (SELECT LastOrginID FROM Configuration))", new List<object>() { pId });
        }
        public static DataSet GetMessagesForPlayer(int pId)
        {
            return TimeHelper.ChangeTime(DAL.GetInstance().GetData(@"SELECT MessageId, (SELECT PlayerName FROM Players WHERE (PlayerId = Messages.FromPId)) AS ""From"", (SELECT PlayerName FROM Players AS Players_1 WHERE (PlayerId = Messages.ToPId)) AS ""To"", 
                Msg as ""Message"", Sent 
                FROM Messages WHERE (ToPId = @param0 ) Order by Sent desc", new List<object>() { pId }), pId);
        }
        public static DataSet GetPlanetData(int sysId, int planetId, int ownerId)
        {
            return DAL.GetInstance().GetData(@"SELECT Planets.PlanetID, Planets.SystemID, Planets.OwnerID, Planets.FleetOwnerID, 
                Planets.ColonyShips, Planets.Transports, Planets.Destroyers, Planets.Crusiers, Planets.Battleships, Planets.Farms, 
                Planets.Factories, Planets.Cybernets, Planets.Labs, Planets.Starbase, Planets.PopulationLevel, Planets.PopToNextLevel, Planets.ProductionPoints, Systems.SystemName 
                FROM Planets INNER JOIN Systems ON Planets.SystemID = Systems.SystemID WHERE (Planets.PlanetID = @param0) AND (Planets.SystemID = @param1) AND (Planets.OwnerID = @param2)",
                                                                                                                                                                                            new List<object>() { planetId, sysId, ownerId });
        }
        public static DataSet GetPlanets(int pId)
        {
            DAL dal = DAL.GetInstance();
            string cmd = "";
            if (dal.DataSource == DataSources.MSSQL)
                cmd = @"SELECT Planets.PlanetID, Planets.SystemID, Planets.OwnerID, Planets.FleetOwnerID, Planets.Farms, Planets.Factories, Planets.Cybernets, Planets.Labs, Planets.Starbase, Planets.PopulationLevel, Planets.PopToNextLevel, Planets.ProductionPoints, Systems.SystemName + ' #' + Cast( Planets.PlanetID as varchar) AS Expr1 FROM Planets INNER JOIN Systems ON Planets.SystemID = Systems.SystemID WHERE (Planets.OwnerID = @param0) ORDER BY Planets.PopulationLevel DESC, Planets.PopToNextLevel";
            else if (dal.DataSource == DataSources.MySQL)
                cmd = @"SELECT Planets.PlanetID, Planets.SystemID, Planets.OwnerID, Planets.FleetOwnerID, Planets.Farms, Planets.Factories, Planets.Cybernets, Planets.Labs, Planets.Starbase, Planets.PopulationLevel, Planets.PopToNextLevel, Planets.ProductionPoints, Cast(Concat(Systems.SystemName, Concat( ' #' , Planets.PlanetID )) as char) AS Expr1 FROM Planets INNER JOIN Systems ON Planets.SystemID = Systems.SystemID WHERE (Planets.OwnerID = @param0) ORDER BY Planets.PopulationLevel DESC, Planets.PopToNextLevel";
            return DAL.GetInstance().GetData(cmd, new List<object>() { pId });
        }
        public static DataSet GetPlayerInfo(int pId)
        {
            return DAL.GetInstance().GetData(@"SELECT PlayerName, ExpPoints, OrginID, CurrentCulture FROM Players WHERE (PlayerId = @param0)", new List<object>() { pId });
        }
        public static DataSet GetPlayerIntel(int pId, int activePlayer)
        {
            return DAL.GetInstance().GetData(@"SELECT Attack, Defense, Speed, Culture, Production, Science, Growth, Biology, Economy, Energy, Math, Physics, Social FROM Players WHERE (PlayerId = @param0) AND (@param0 = @param1) OR (PlayerId = @param0) AND ((SELECT Biology FROM Players AS Players_1 WHERE (PlayerId = @param1)) >= Biology + (6 -
                          (SELECT     Academy
                            FROM          Players AS Players_2
                            WHERE      (PlayerId = @param1))))", new List<object>() { pId, activePlayer });
        }
        public static DataSet GetSciences(int pId)
        {
            return DAL.GetInstance().GetData(@"SELECT Biology, Economy, Energy, Math, Physics, Social, CurrentCulture FROM Players WHERE (PlayerId = @param0)", new List<object>() { pId });
        }
        public static DataSet GetSystemView(int sId)
        {
            return DAL.GetInstance().GetData(@"SELECT Planets.PlanetID, Planets.PopulationLevel, Players.PlayerName, Players.PlayerId, Floor(Planets.Starbase) AS Starbase FROM Planets INNER JOIN Players ON Planets.OwnerID = Players.PlayerId WHERE (Planets.SystemID = @param0)", new List<object>() { sId });
        }
        public static DataSet GetAllianceMembers(int aId)
        {
            return DAL.GetInstance().GetData(@"SELECT Players.PlayerName, Players.PlayerId FROM AllianceMembers INNER JOIN Players ON AllianceMembers.PlayerId = Players.PlayerId WHERE (AllianceMembers.AllianceId = @param0)", new List<object>() { aId });
        }
        public static void DeleteMessage(int MessageId)
        {
            DAL.GetInstance().DeleteData("Delete from Messages Where MessageId = @param0", new List<object>() { MessageId });
        }
        public static DataSet GetAllianceIncomings(int aId, int pId)
        {
            return TimeHelper.ChangeTime(DAL.GetInstance().GetData("SELECT News.NewsDate AS \"Time\", Players_2.PlayerName AS Player, News.Message FROM News INNER JOIN Players AS Players_2 ON News.PlayerId = Players_2.PlayerId INNER JOIN AllianceMembers ON AllianceMembers.PlayerId = Players_2.PlayerId WHERE (News.Message LIKE 'Incoming fleet of%') AND (AllianceMembers.AllianceId = @param0)", new List<object>() { aId }), pId);
        }
    }
}
